<?php
/**
 * Database Query Builder
 *
 * @subpackage Database
 * @version 0.2
 * @author bluer
 */

class Builder {
    /**
     * The database connection instance.
     *
     * @var Apdb instance
     */
    protected $conn;

    /**
     * The database query post processor instance.
     *
     * @var Processors instance
     */
    protected $processor;

    /**
     * The table which the query is targeting.
     *
     * @var string
     */
    protected $from;

    /**
     * The columns that should be returned.
     *
     * @var array
     */
    protected $columns = array();

    /**
     * The where constraints for the query.
     *
     * @var array
     */
    protected $wheres = array();

    /**
     * The orderby for the query.
     *
     * @var string
     */
    protected $orderBy;
    
    /**
     * The groupBy for the query.
     *
     * @var string
     */
    protected $groupBy;

    /**
     * The maximum number of records to return.
     *
     * @var int
     */
    protected $limit;

    /**
     * The number of records to skip.
     *
     * @var int
     */
    protected $offset;

    /**
     * The current query value bindings.
     *
     * @var array
     */
    protected $bindings = array();
    
    /**
     * All of the available clause operators.
     *
     * @var array
     */
    protected $operators = array(
        '=', '<', '>', '<=', '>=', '<>', '!=',
        'like', 'not like', 'between', 'ilike',
        '&', '|', '^', '<<', '>>',
        'rlike', 'regexp', 'not regexp',
        '~', '~*', '!~', '!~*'
    );
    
    /**
     * The Method for query.
     *
     * @see Apdb Class
     * @var string 
     */
    protected $queryMethod;
    
    /**
     * The raw sql for query
     *
     * @var string
     */
    protected $rawQuery;
    
    /**
     * Whether use cache for result
     *
     * @var bool
     */
    protected $useCache = false;
    
    /**
     * The cache expiration
     *
     * @var int
     */
    protected $expires;
    
    /**
     * The cache key
     *
     * @var string
     */
    protected $cacheKey;

    /**
     * Whether to show the log
     *
     * @var bool
     */
    public $debug = false;
    
    
    public function __construct($connection, Processor $processor) {
        $this->conn = $connection;
        $this->processor = $processor;
        $this->queryMethod = 'getResults';
    }

    /**
     * Determine if the given operator and value combination is legal.
     *
     * @param string $operator
     * @param mixed $value
     * @return bool
     */
    protected function validOperatorAndValue($operator, $value) {
        $isOperator = in_array(strtolower($operator), $this->operators);

        return ($isOperator && !is_null($value));
    }

    /**
     * Get the SQL representation of the query.
     *
     * @return string
     */
    protected function toSql() {
        $where = '';
        if (count($this->wheres) > 0) {
            $where = 'WHERE ' . $this->compileWheres();
        }

        $sql = sprintf("SELECT %s FROM `%s` %s",
            implode(',', $this->columns), $this->from, $where
        );

        if ( !is_null($this->groupBy) ) {
            $sql .= ' ' . $this->groupBy . ' ';
        }

        if ( !is_null($this->orderBy) ) {
            $sql .= ' ' . $this->orderBy . ' ';
        }

        if ( !is_null($this->limit) ) {
            $this->offset = is_null($this->offset) ? 0 : $this->offset;
            $sql .= ' LIMIT ' . $this->offset . ',' . $this->limit;
        }

        return $sql;
    }

    /**
     * Compile the "where" portions of the query.
     *
     * @return int|mixed
     */
    protected function compileWheres() {
        if (count($this->wheres)) {
            $sql = '';
            foreach ($this->wheres as $where) {
                if ($where['type'] == 'All') return 1;

                if ($where['type'] == 'Raw') {
                    $sql .= ' ' . $where['relation'] . ' ' . $where['sql'];
                } else {
                    $method = '_where' . $where['type'];
                    $sql .= ' ' . $this->$method($where) . ' ';
                }
            }

            return preg_replace('/^ (and |or )/i', '', $sql, 1);
        }

        return '';
    }
    
    /**
     * Get the key of the namespace version 
     *
     * @access protected
     * 
     * @param string $table The table name
     * @return string
     */
    protected static function getNSVersionKey($table = null) {
        return is_null($table) ? 'raw_ns_ver' : ($table . '_ns_ver');
    }
    
    /**
     * Get the key of the user namespace version
     *
     * @access protected
     * @return string
     */
    protected static function getUserNSVersionKey($user_id) {
        return 'user_' . $user_id . '_ns_ver';
    }
    
    /**
     * Get name space for cache
     *
     * @access protected
     * @return string|false 符合缓存条件时返回相应的namespace，否则返回false
     */
    protected function getNameSpace() {
        $nsVerKey = $this->getNSVersionKey($this->from);
        if (($version = cacheGet($nsVerKey)) == null) {
            cacheSet($nsVerKey, $version = time(), 0);
        }
        
        $table = is_null($this->from) ? 'raw' : $this->from;
        
        // 针对单个用户信息缓存，使用二级命名空间，仅支持基于单个用户ID的查询
        if (isset($this->bindings[':user_id'])) {
            // 同时查询多个用户时不启用自动缓存
            if (isset($this->bindings[':user_id_1'])) return false;
            
            $key = $this->getUserNSVersionKey( $this->bindings[':user_id'] );
            if (($subversion = cacheGet($key)) == null) {
                cacheSet($key, $subversion = time(), 3600 * 24 * 7); // 默认缓存1周
            }
            return $table . '_' . $version . '_' . $subversion . ':';
        }
        
        return $table . '_' . $version . ':';
    }
    
    /**
     * Set the key for cache
     *
     * @access protected
     * @param string $query Teh pending SQL statement
     */
    protected function setCacheKey(&$query) {
        if ($ns = $this->getNameSpace()) {
            $this->cacheKey = md5($ns . $query);
        }
    }
    
    /**
     * Generate a placeholder for the parameter.
     *
     * @access protected
     * 
     * @param string $param
     * @return string $placeholder
     */
    protected function generatePlaceholder($param) {
        for ($i=0; $i<99; $i++) {
            $placeholder = ($i==0) ? (':' . $param) :  (':' . $param . '_' . $i);
            if (!isset($this->bindings[$placeholder])) return $placeholder;
        }
    }

    /**
     * Set the table which the query is targeting.
     *
     * @param string $table
     * @return $this
     */
    public function from($table = null) {
        if ($table != null) $this->from = $table;
        
        return $this;
    }

    /**
     * Compile a basic where clause.
     *
     * @param array $where
     * @return false|null|string
     */
    protected function _whereBasic($where) {
        // 嵌套查询使用prepare模式代替bind
        if (isset($where['prepare']) && $where['prepare']) {
            $query = "{$where['relation']} `{$where['column']}` {$where['operator']} %s";
            return $this->conn->prepare($query, $where['value']);
        }

        $placeholder = $this->generatePlaceholder($where['column']);
        $this->bindings[$placeholder] = $where['value'];

        return "{$where['relation']} `{$where['column']}` {$where['operator']} {$placeholder}";
    }

    /**
     * Compile a "where in" clause.
     *
     * @param array $where
     * @return string
     *
     */
    protected function _whereIn($where) {
        $inQuery = implode(',', array_fill(0, count($where['values']), '%s'));
        $inQuery = $this->conn->prepare($inQuery, $where['values']);

        return "{$where['relation']} `{$where['column']}` {$where['operator']} ($inQuery)";
    }

    /**
     * Compile a nested where clause.
     *
     * @param array $where
     * @return string
     */
    protected function _whereNested($where) {
        if (count($where['wheres']) == 0) return '';

        $sql = '';
        foreach ($where['wheres'] as $w) {
            $method = '_where' . $w['type'];
            $w['prepare'] = true;
            $sql .= ' ' . $this->$method($w) . ' ';
        }

        return $where['relation'] . ' (' . preg_replace('/^ (and |or )/i', '', $sql, 1) . ')';
    }

    /**
     * Add a nested where statement to the query.
     *
     * @param Closure $callback
     * @param string $relation
     * @return $this
     */
    protected function whereNested(Closure $callback, $relation = 'and') {
        $query = new Builder($this->conn, $this->processor);

        call_user_func($callback, $query);

        if (count($query->wheres)) {
            $type = 'Nested';
            $wheres = $query->wheres;
            $this->wheres[] = compact('type', 'wheres', 'relation');
        }

        return $this;
    }

    /**
     * Get the result from each sublist
     *
     * @param array $inGroups
     * @return array
     */
    protected function _sublistGet(&$inGroups) {
        $data = array();

        foreach ($inGroups as $table => $where) {
            $this->from = $table;
            array_push($this->wheres, $where);
            $result = $this->execute($query = $this->toSql());
            array_pop($this->wheres);
            $this->bindings = array();

            if($result) $data = array_merge($data, $result);
        }

        return $data;
    }

    /**
     * Execute the query as a "select" statement.
     *
     * @param string $query
     * @return mix
     */
    protected function execute($query) {
        if ($this->debug) $this->showLog($query);

        if ($this->useCache) {
            if ($this->cacheKey == null) {
                $cacheKey = $query . ':' . implode(',', $this->bindings);
                $this->setCacheKey($cacheKey);
            }

            if (!is_null($this->cacheKey)) {
                if (($data = cacheGet($this->cacheKey)) == null) {
                    $data = $this->conn->{$this->queryMethod}($query, $this->bindings);
                    cacheAdd($this->cacheKey, $data, $this->expires);
                }
                return $data;
            }
        }

        return $this->conn->{$this->queryMethod}($query, $this->bindings);
    }

    /**
     * Add a basic where clause to the query.
     *
     * @param string|array  $column 字段名。当为数组时，则认为是key-value模式的多条件查询。
     * @param string  $operator 操作符
     * @param mixed   $value 值
     * @param string  $relation 指定多个条件查询的条件关系。默认: and
     *
     * @return $this
     */
    public function where($column, $operator = null, $value = null, $relation = 'and') {
        if (is_numeric($column) && $column == 1) {
            $this->wheres[] = array('type' => 'All', 'value'=>1);
            return $this;
        }

        if ($column instanceof Closure) {
            return $this->whereNested($column, $relation);
        }

        if (is_array($column)) {
            return $this->whereNested(function($query) use ($column) {
                foreach ($column as $key => $value)
                {
                    $query->where($key, '=', $value);
                    if ($key == 'user_id') {
                        $this->from = $this->processor->switchTable($this, $value);
                    }
                }
            }, $relation);
        }

        if (! in_array(strtolower($operator), $this->operators, true)) {
            list($value, $operator) = array($operator, '=');
        }

        if ($this->validOperatorAndValue($operator, $value)) {
            $type = 'Basic';
            $this->wheres[] = compact('type', 'column', 'operator', 'value', 'relation');
            if ($column == 'user_id') {
                $this->from = $this->processor->switchTable($this, $value);
            }
        }
        
        return $this;
    }

    /**
     * Add an "or where" clause to the query.
     *
     * @param string $column
     * @param string $operator
     * @param mixed $value
     *
     * @return $this
     */
    public function orWhere($column, $operator = null, $value = null) {
        return $this->where($column, $operator, $value, 'or');
    }

    /**
     * Add a "where in" clause to the query.
     *
     * @param string $column
     * @param array $values
     * @param string $relation
     * @param bool $not
     * @return $this
     */
    public function whereIn($column, $values, $relation = 'and', $not = false) {
        $type = 'In';
        $operator = $not ? 'NOT IN' : 'IN';

        if ($column == 'user_id' && in_array($this->from, $this->conn->getMultiTables())) {
            $where = compact('type', 'column', 'values', 'relation', 'operator');
            $this->processor->processWhereIn($this, $where);
        } else {
            $this->wheres[] = compact('type', 'column', 'values', 'operator', 'relation');
        }

        return $this;
    }

    /**
     * Add a "where not in" clause to the query.
     *
     * @param string $column
     * @param array $values
     * @param string $relation
     * @return Builder
     */
    public function whereNotIn($column, $values, $relation = 'and') {
        return $this->whereIn($column, $values, $relation, true);
    }

    /**
     * Add a raw where clause to the query.
     *
     * @param string $sql
     * @param array $bindings
     * @param string $relation
     * @return $this
     */
    public function whereRaw($sql, $bindings = array(), $relation = 'and') {
        $type = 'Raw';
        $this->wheres[] = compact('type', 'sql', 'relation');
        if (count($bindings) > 0) {
            $this->bindings = array_merge($this->bindings, $bindings);
        }

        return $this;
    }

    /**
     * Set the columns to be selected.
     *
     * @param array|string  $columns 可以用数组，也可以传多个参数的形式
     * @return $this
     */
    public function select($columns = array('*')) {
        $this->columns = is_array($columns) ? $columns : func_get_args();
        
        return $this;
    }

    /**
     * Set the "offset" value of the query.
     *
     * @param int $value
     * @return $this
     */
    public function offset($value) {
        $this->offset = $value > 0 ? $value : 0;
        
        return $this;
    }

    /**
     * Set the "limit" value of the query.
     *
     * @param int $value
     * @return $this
     */
    public function limit($value) {
        $this->limit = $value > 0 ? $value : 15;
        
        return $this;
    }

    /**
     * Set the limit and offset for a given page.
     *
     * @param int $page
     * @param int $perPage
     * @return Builder
     */
    public function forPage($page, $perPage = 15) {
        $this->offset( $perPage * ($page - 1) );
        
        return $this->limit($perPage);
    }

    /**
     * Add an "order by" clause to the query.
     *
     * @param string $column
     * @param string $order
     * @return $this
     */
    public function orderBy($column, $order = 'asc') {
        $order = strtolower($order) == 'asc' ? 'ASC' : 'DESC';
        $this->orderBy = "ORDER BY `$column` $order";
        
        return $this;
    }

    /**
     * Add an "group by" clause to the query.
     *
     * @param string|array $column
     * @return $this
     */
    public function groupBy($column) {
        if (is_array($column)) {
            $column = implode(',', $column);
        }
        $this->groupBy = 'GROUP BY ' . $column;
        
        return $this;
    }

    /**
     * Get data from cache if exists
     *
     * @param string $key 缓存key。不指定则默认使用内部的机制生成
     * @param int $expire 缓存时间
     * @return $this
     */
    public function cache($key = null, $expire = 3600) {
        if (function_exists('cacheAdd')) {
            $this->useCache = true;
            $this->expires = $expire;
            $this->cacheKey = (!is_null($key) && trim($key)=='') ? null : $key;
        }
        
        return $this;
    }

    /**
     * Execute the query as a "select" statement.
     *
     * @param array $columns
     * @return array|mix
     */
    public function get($columns = array('*')) {
        if ($this->columns == null) {
            $this->columns = is_array($columns) ? $columns : func_get_args();
        }

        // 针对 user_id IN(...) 的查询处理
        $inGroups = $this->processor->getInGroups();
        if (count($inGroups) > 0) {
            return $this->_sublistGet($inGroups);
        }

        $query = $this->rawQuery ? :$this->toSql();

        return $this->execute($query);
    }
    
    /**
     * Get the single value of a given column
     *
     * @param string $column
     * @return string
     */
    public function value($column) {
        $this->queryMethod = 'getVar';
        
        return $this->get($column);
    }
    
    /**
     * Get an array with the values of a given column.
     *
     * @param string $column
     * @return array|false
     */
    public function lists($column) {
        $this->queryMethod = 'getCol';
        
        return $this->get($column);
    }
    
    /**
     * Get one row object
     *
     * @return object|false
     */
    public function first() {
        $this->queryMethod = 'getRow';
        
        return $this->get();
    }

    /**
     * Execute the raw queries (Not Recommended)
     *
     * @param string $sql
     * @return $this
     */
    public function raw($sql) {
        if (empty($sql)) return false;
        
        $this->rawQuery = $sql;
        
        return $this;
    }
    
    /**
     * Insert a row into a table
     *
     * @param array $data  Data to insert (in column => value pairs).
     * @return bool
     */
    public function insert($data) {
        if (empty($data)) return false;

        return $this->conn->insert($this->from, $data);
    }

    /**
     * Batch insert rows into a table
     *
     * @param array $data 格式:
     *  array(
     *      array(column => value, ...),
     *      array(column => value, ...)
     *  )
     *
     * @return bool
     */
    public function batchInsert($data) {
        if (empty($data)) return false;

        $this->conn->beginTransaction();
        foreach ($data as $one) {
            if ($this->insert($one) === false) {
                $errors[] = $one;
            }
        }

        if (empty($errors)) {
            $this->conn->endTransaction();
            return true;
        }

        $this->conn->cancelTransaction();

        if ($this->debug) {
            foreach ($errors as $msg) {
                if (is_array($msg)) $msg = implode(',', $msg);
                $this->showLog($msg);
            }
        }

        return false;
    }
    
    /**
     * Update a row in the table
     *
     * @param array $data
     * @return int|false The number of rows updated, or false on error.
     */
    public function update($data) {
        if (empty($data)) return false;
        
        foreach ($data as $field => $value) {
            $placeholder = $this->generatePlaceholder($field);
			$fields[] = "`$field`=$placeholder";
			$this->bindings[$placeholder] = $value;
		}

        $where = $this->compileWheres();
        if ($where == '') return false;

        $sql = "UPDATE `{$this->from}` SET " . implode(',', $fields) . " WHERE {$where}";
        if ($this->debug) $this->showLog($sql);
        
        return $this->conn->query($sql, $this->bindings);
    }
    
    /**
     * Delete a row in the table
     *
     * @return int|false The number of rows deleted, or false on error.
     */
    public function delete() {
        $where = $this->compileWheres();
        if ($where == '') return false;
        
        $sql = "DELETE FROM `{$this->from}` WHERE {$where}";
        if ($this->debug) $this->showLog($sql);
        
		return $this->conn->query($sql, $this->bindings);
    }

    /**
     * Clear the cache by namespace,user_id or explicit key
     *
     * @param string $key 表名，或确切的key，纯数字用户ID
     * @param bool $isNamespace 是否基于命名空间的批量删除
     * @return bool
     */
    public function flushCacheBy($key, $isNamespace = false) {
        if ($isNamespace) {
            // 清除指定用户的相关缓存
            if (is_numeric($key)) return cacheDelete( $this->getUserNSVersionKey($key) );
            
            // 清除指定表的相关缓存
            return cacheDelete( $this->getNSVersionKey($key) );
        }
        
        return cacheDelete($key);
    }

    /**
     * Get the sub list of the table.
     *
     * Dependent on USER_AUTH_INFO table
     *
     * @param int $userId Optional. 如给定则返回用户所对应的表
     * @return mixed
     */
    public function sublist($userId = 0) {
        return $this->processor->processSublist($this->from, $userId);
    }

    /**
     * Get the table which the query is targeting.
     *
     * @return string
     */
    public function getFrom() {
        return $this->from;
    }

    /**
     * Get the database connection instance.
     *
     * @return Apdb
     */
    public function getConnection() {
        return $this->conn;
    }

    /**
     * Get the database query processor instance.
     *
     * @return Processor
     */
    public function getProcessor() {
        return $this->processor;
    }

    /**
     * Whether enable debug mode
     *
     * @param bool $enable
     * @return $this
     */
    public function debug($enable) {
        $this->debug = $enable;

        return $this;
    }

    /**
     * Print the SQL statements which is finally executed.
     *
     * @param string $msg
     */
    protected function showLog($msg) {
        echo $msg,"<br>";
        if ($this->bindings) {
            print_r($this->bindings);
            echo '<br>';
        }
    }
}
?>